﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using MySql.Data.MySqlClient;
using MySql.Web.Common;


/// <summary>
/// Summary description for HAInfoService
/// </summary>
/// 

//Class user for make, create a connection to HAInfo database from RAI-SERVER
//Make change, update, add data to this Database
//Im summary, this class use for operating with HAInfo's data.
public class HAInfoService
{
    //Connection string 
    private static MySqlConnection connection = new MySqlConnection();
    //Represents an in-memory cache of data for hold some data that got from HAInfo DB
    private static DataSet hainfoDataset = new DataSet();
    
    /// <summary>
    ///return connection string
    /// </summary>
    /// <returns>
    /// strConnection: connectionToDB string 
    /// </returns>
     static String GetConnStr()
    {
        String strConnection = "";
         //get Connection string from web.config file
        strConnection = ConfigurationManager.AppSettings["ConnectionString"];

         //return that string
        return strConnection;
    }
   
    /// <summary>
     /// Create a function to manipulate MySQL Database (in this case, HAInfo)
    /// </summary>
    /// <returns>
    ///  instance of MySQLConnection
    /// </returns>
    static MySqlConnection MySQLConnection()
    {
        //get connection string from web.config and assign it to strProvider.
        string strProvider = GetConnStr();
        //temp MySQLConnection object type with settings and parameter as below
        MySqlConnection mysqlCon;
        try
        {   
            //Create a connection to HAInfo with this connection stringb (was suppiled by strProvider)
            mysqlCon = new MySqlConnection(strProvider);

        }
        catch
        {
            //If any error occur, assign null to MySQLConnection - return value
            return null;
        }
        //else return a connection with this connection string
        return mysqlCon;
    }
    // 
    /// <summary>
    /// Contructor of HAInfoService
    /// </summary>
	public HAInfoService()
	{
		//
		// TODO: Add constructor logic here
		//
        // Assign MySQLConnection to conn to make use.
        connection = MySQLConnection();
        //Fullfill data
        FillDataset();
	}
    
    /// <summary>
    /// //Get data from HAInfo then fill it to some data container
    /// </summary>
    /// <returns>
    /// true for success
    /// false for fail
    /// </returns>
    public bool FillDataset()
    {
        try
        {
            //Open connection
            connection.Open();
            //string for select all Students that staying RTD at current time.
            String getAvailableUsers = "select person.ID, person.name, person.Account, batchdraft.name as FU"
                                        + " from person Inner Join batchdraft ON person.Id = batchdraft.PersonId"
                                        + " where  (batchdraft.Name = 'FU.K4c' or batchdraft.Name = 'FU.K5a')"
                                        + " and person.id not in  (SELECT personchange.perId"
                                                                    + " FROM personchange"
                                                                    + " WHERE personchange.ToOrgId <>  '2' AND"
                                                                    + " (personchange.EndDate  is  null or personchange.EndDate > curdate() )"
                                                                    + " GROUP BY  personchange.perId)"
                                        + " GROUP BY  person.Id"
                                        + " ORDER BY batchdraft.name,person.Account ASC";
            //Make a MySQLcommand for using command with above query and connection string 
            MySqlCommand cmGetUser = new MySqlCommand(getAvailableUsers, connection);
            //Represents a set of SQL commands and a database connection 
            //that are used to fill the DataSet and update the data source.
            MySqlDataAdapter daGetUser = new MySqlDataAdapter(cmGetUser);
            //Clear static instance of HAInfoDataset for an empty status
            hainfoDataset.Clear();
            //Adds or refreshes rows in the System.Data.DataSet to match those in the data
            //source
            daGetUser.Fill(hainfoDataset,"Users at RTD");
       
        }//trap error
        catch
        {
            //false show that can't not fill data into datasource
            return false;
        }
        finally
        {
            //Whatever, close connection
            connection.Close();
        }
        //fill data success
        return true;

    }
    
    /// <summary>
    /// Return a table with Namelist of Users
    /// </summary>
    /// <returns>
    /// 
    /// </returns>
    public DataTable GetUsersAtRtd()
    {
        //Table[0] is only table of this dataset
        return hainfoDataset.Tables[0].Copy() ;
    }
    //Return id of user with username in table 
    public long GetUserId(String strUserName)
    {
        //assign to make a flag
        long lId = -1;
        //create a table with listname contained.
        DataTable dtUsers = GetUsersAtRtd(); ;
        //if table has value
        if (dtUsers != null)
        {
            //check all row in table
            for (int i = 0; i < dtUsers.Rows.Count; i++)
            {
                //Check equal username input with username in table to get the ID   
                if (strUserName.Trim().ToLower().Equals(dtUsers.Rows[i][2].ToString().Trim().ToLower()))
                {
                    //Assign ID
                    lId = long.Parse(dtUsers.Rows[i][0].ToString());
                }
            }
        }
        //return ID
        return lId; ;
    }
    
    /// <summary>
    /// Get username from user's ID
    /// </summary>
    /// <param name="lId"></param>
    /// <returns>
    /// return Account of correspond ID, such as "JobsSP" for "Stephen Paul Jobs"
    /// </returns>
    public string GetUserAccount(long lId)
    {
        //assign null
        string strUserAccount = null;
        //Gets an array of all System.Data.DataRow objects that have right ID
        DataRow[] drUser = hainfoDataset.Tables[0].Select("ID = '" + lId + "'");
        //if r has at least one item 
        if(drUser.Length>0)
        {
        //get username correspond with that ID and assign it to useAccount
        strUserAccount = drUser[0][2].ToString();
        //return value
        return strUserAccount;
        }
        //if there's nothing, return null
        return null;
    }
}
